﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[fnBkp_GetAllBackups]
#-- Purpose:		Provides a mechanism query for a full list of database backups
#--	Last Update:	04/25/2013
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE FUNCTION [dbo].[fnBkp_GetAllBackups]
(
	@database_name		sysname,
	@start_date			datetime		= NULL,
	@label				nvarchar(255)	= NULL,
	@files_must_exist	bit				= 1,
	@recovery_window	bit				= 1
)
RETURNS @returntable TABLE 
(
	id							bigint					PRIMARY KEY,
	recovery_path_broken		bit,
	backup_set_id				int,
	db_backup_set_id			int,
	prev_tlog_backup_set_id		int,
	database_name				nvarchar(128),
	name						nvarchar(128),
	type						char(1),
	backup_start_date			datetime,
	has_bulk_logged_data		bit,
	backup_size					numeric(20,0),
	physical_device_name		nvarchar(260),
	file_exists					bit,
	database_backup_lsn			numeric(25,0),
	differential_base_lsn		numeric(25,0),
	checkpoint_lsn				numeric(25,0),
	fork_point_lsn				numeric(25,0),
	first_lsn					numeric(25,0),
	last_lsn					numeric(25,0),
	first_recovery_fork_guid	uniqueidentifier,
	last_recovery_fork_guid		uniqueidentifier
)
AS
BEGIN
IF LTRIM(RTRIM(@label)) = ''
	SET @label = NULL

IF @start_date IS NOT NULL
  BEGIN
	IF ISNULL(@files_must_exist, 0) = 0
		INSERT INTO @returntable
			SELECT		id,
						recovery_path_broken,
						backup_set_id,
						db_backup_set_id,
						prev_tlog_backup_set_id,
						database_name,
						name,
						type,
						backup_start_date,
						has_bulk_logged_data,
						backup_size,
						physical_device_name,
						file_exists,
						database_backup_lsn,
						differential_base_lsn,
						checkpoint_lsn,
						fork_point_lsn,
						first_lsn,
						last_lsn,
						first_recovery_fork_guid,
						last_recovery_fork_guid
			FROM		[dbo].[fnBkp_GetBackupList_Date](@database_name, @start_date) t1
			WHERE		ISNULL(name, '') = ISNULL(@label, ISNULL(name, ''))
	ELSE
		INSERT INTO @returntable
			SELECT		id,
						recovery_path_broken,
						backup_set_id,
						db_backup_set_id,
						prev_tlog_backup_set_id,
						database_name,
						name,
						type,
						backup_start_date,
						has_bulk_logged_data,
						backup_size,
						physical_device_name,
						file_exists,
						database_backup_lsn,
						differential_base_lsn,
						checkpoint_lsn,
						fork_point_lsn,
						first_lsn,
						last_lsn,
						first_recovery_fork_guid,
						last_recovery_fork_guid
			FROM		[dbo].[fnBkp_GetBackupList_Date](@database_name, @start_date) t1
			WHERE		file_exists = 1
						AND ISNULL(name, '') = ISNULL(@label, ISNULL(name, ''))
  END
ELSE IF ISNULL(@recovery_window, 1) = 1
  BEGIN
	IF ISNULL(@files_must_exist, 0) = 0
		INSERT INTO @returntable
			SELECT		id,
						recovery_path_broken,
						backup_set_id,
						db_backup_set_id,
						prev_tlog_backup_set_id,
						database_name,
						name,
						type,
						backup_start_date,
						has_bulk_logged_data,
						backup_size,
						physical_device_name,
						file_exists,
						database_backup_lsn,
						differential_base_lsn,
						checkpoint_lsn,
						fork_point_lsn,
						first_lsn,
						last_lsn,
						first_recovery_fork_guid,
						last_recovery_fork_guid
			FROM		[dbo].[fnBkp_GetBackupList_RecoveryWindow](@database_name) t1
			WHERE		ISNULL(name, '') = ISNULL(@label, ISNULL(name, ''))
	ELSE
		INSERT INTO @returntable
			SELECT		id,
						recovery_path_broken,
						backup_set_id,
						db_backup_set_id,
						prev_tlog_backup_set_id,
						database_name,
						name,
						type,
						backup_start_date,
						has_bulk_logged_data,
						backup_size,
						physical_device_name,
						file_exists,
						database_backup_lsn,
						differential_base_lsn,
						checkpoint_lsn,
						fork_point_lsn,
						first_lsn,
						last_lsn,
						first_recovery_fork_guid,
						last_recovery_fork_guid
			FROM		[dbo].[fnBkp_GetBackupList_RecoveryWindow](@database_name) t1
			WHERE		file_exists = 1
						AND ISNULL(name, '') = ISNULL(@label, ISNULL(name, ''))
  END
ELSE
  BEGIN
	IF ISNULL(@files_must_exist, 0) = 0
		INSERT INTO @returntable
			SELECT		id,
						recovery_path_broken,
						backup_set_id,
						db_backup_set_id,
						prev_tlog_backup_set_id,
						database_name,
						name,
						type,
						backup_start_date,
						has_bulk_logged_data,
						backup_size,
						physical_device_name,
						file_exists,
						database_backup_lsn,
						differential_base_lsn,
						checkpoint_lsn,
						fork_point_lsn,
						first_lsn,
						last_lsn,
						first_recovery_fork_guid,
						last_recovery_fork_guid
			FROM		[dbo].[fnBkp_GetBackupList](@database_name, NULL, NULL, @label) t1
	ELSE
		INSERT INTO @returntable
			SELECT		id,
						recovery_path_broken,
						backup_set_id,
						db_backup_set_id,
						prev_tlog_backup_set_id,
						database_name,
						name,
						type,
						backup_start_date,
						has_bulk_logged_data,
						backup_size,
						physical_device_name,
						file_exists,
						database_backup_lsn,
						differential_base_lsn,
						checkpoint_lsn,
						fork_point_lsn,
						first_lsn,
						last_lsn,
						first_recovery_fork_guid,
						last_recovery_fork_guid
			FROM		[dbo].[fnBkp_GetBackupList](@database_name, NULL, NULL, @label) t1
			WHERE		file_exists = 1
  END

  RETURN
END
